

INSERT INTO sales_behaivor_history 
    (purchase_id, sale_id, customer_id, item_id, profit, roi, date)
SELECT 
    pvi.purchaseId AS purchase_id,
    s.id AS sale_id,
    s.customer_id,
    pvi.item_id,

    round((((svi.qty - svi.returned_qty) * svi.price) - (svi.qty * (pvi.unit_price + (coalesce(pvi.cogs_row_amount / NULLIF((pvi.qty-pvi.returned_qty), 0), 0))))),2) AS profit,
    
	
	ROUND(CASE 
	    WHEN ((svi.qty - svi.returned_qty) * 
	          (pvi.unit_price + COALESCE(pvi.cogs_row_amount / NULLIF(pvi.qty - pvi.returned_qty, 0), 0))
	         ) > 0
	    THEN 
	        (
	            (
	                ((svi.qty - svi.returned_qty) * svi.price)
	                -
	                ((svi.qty - svi.returned_qty) * 
	                    (pvi.unit_price + COALESCE(pvi.cogs_row_amount / NULLIF(pvi.qty - pvi.returned_qty, 0), 0))
	                )
	            )
	            /
	            NULLIF(
	                ((svi.qty - svi.returned_qty) * 
	                    (pvi.unit_price + COALESCE(pvi.cogs_row_amount / NULLIF(pvi.qty - pvi.returned_qty, 0), 0))
	                ),
	                0
	            )
	        ) * 100
	    ELSE 0 
	end,2) AS roi,
	
    s.sale_date AS date
FROM sale_vs_item svi
INNER JOIN sale s ON svi.saleId = s.id and s.deleted = 0
INNER JOIN purchase_vs_item pvi ON svi.purchase_vs_itemId = pvi.id
inner join purchase p on p.id = pvi.purchaseId and  p.deleted = 0
WHERE s.deleted = 0 
  AND svi.deleted = 0
  AND pvi.deleted = 0;